﻿using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Data;
using MySql.Data.MySqlClient;

namespace Flockmaster
{
    public class MySql : DatabaseCopy,IDatabaseCopy
    {
        public MySql()
        {
            dest_Type = DBTypes.MySql;
        }

        public override bool DoesTableExist(string TableName, DBTypes m_dbType)
        {
            return base.DoesTableExist(TableName, m_dbType);
        }

        public override void DropTable(string TableName)
        {
            base.DropTable(TableName);
        }

        /// <summary>
        /// 创建目标数据库连接**********************
        /// </summary>
        /// <param name="IpAddress"></param>
        /// <param name="Username"></param>
        /// <param name="Password"></param>
        /// <param name="Database"></param>
        public  void CreateDestConn(string IpAddress, string Username, string Password, string Database)
        {
            string connStr = String.Format("server={0};Database={3};uid={1};pwd={2};charset=gb2312", IpAddress, Username, Password, Database);
            destConnection = new MySqlConnection(connStr);
        }

        public void CreateDestConn(string IpAddress, string Username, string Password)
        {
            string Database = "information_schema";
            string connStr = String.Format("server={0};Database={3};uid={1};pwd={2};charset=utf8;Connection Timeout=5;", IpAddress, Username, Password, Database);
            sysConnection = new MySqlConnection(connStr);
        }

        public override void CopyTable(SendCmd Message)
        {
            base.CopyTable(Message);
        }

        protected override void BackWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            base.BackWorker_ProgressChanged(sender, e);
        }

        protected override void BackWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            base.BackWorker_RunWorkerCompleted(sender, e);
        }

        protected override void BackWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            SendCmd Message;
            try
            {
                Message = ((SendCmd)e.Argument);
            }
            catch
            {
                throw new Exception("调用复制函数时，提供的参数格式不符，请检查！");
                return;
            }
            string TableName = Message.TableName.ToString();
            string query = Message.SqlCmd;
            string field, param;
            DataTable scheamTable;
            bool IsKey = false;
            try
            {
                using (IDbCommand sqlCmd = this.srcConnection.CreateCommand())
                {
                    sqlCmd.CommandText = query;
                    IDataReader srcReader = sqlCmd.ExecuteReader(CommandBehavior.KeyInfo);
                    scheamTable = srcReader.GetSchemaTable();

                    // 创建 SQL Server Compact Edition 表结构
                    using (IDbCommand destCmd = this.destConnection.CreateCommand())
                    {
                        string cmd = GenerateTableSchemaSql(scheamTable, ref IsKey);
                        if (cmd == String.Empty)
                        {
                            return;
                        }
                        destCmd.CommandText = cmd;
                        destCmd.ExecuteNonQuery();
                    }



                    // 生成 SQL Server Compact Edition 数据插入 SQL 语句
                    StringBuilder sbFields = new StringBuilder();
                    StringBuilder sbParams = new StringBuilder();

                    using (MySqlCommand destCommand = (MySqlCommand)destConnection.CreateCommand())
                    {
                        if (!Message.IsOnlyTableStructure)
                        {
                            DataRow schemaRow;
                            for (int i = 0; i < scheamTable.Rows.Count; i++)
                            {
                                if (i != 0)
                                {
                                    sbFields.Append(", ");
                                    sbParams.Append(", ");
                                }

                                schemaRow = scheamTable.Rows[i];
                                field = string.Format("{0}", schemaRow["ColumnName"]); //字段名称
                                param = "@" + ((string)schemaRow["ColumnName"]).Replace(" ", "_"); //参数名称
                                sbFields.Append(field);
                                sbParams.Append(param);
                                destCommand.Parameters.AddWithValue(param, null);
                            }

                            string insertSql = string.Format("INSERT INTO {0}({1}) VALUES({2})", TableName, sbFields, sbParams);
                            destCommand.CommandText = insertSql;
                            //            Debug.WriteLine(insertSql);
                            count = 0;
                            // 执行数据导入
                            object[] values = new object[scheamTable.Rows.Count];
                            int errorcount = 0;
                            while (srcReader.Read())
                            {
                                //values = new object[srcReader.FieldCount];
                                try
                                {
                                    srcReader.GetValues(values);
                                }
                                catch (Exception ex)
                                {
                                    BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "从" + TableName + "读取数据时,发生" + ex.Message);
                                    if (errorcount < 200)
                                    {
                                        continue;
                                    }
                                    else if (errorcount == 200)
                                    {
                                        BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "从" + TableName + "读取数据时,发生了太多的错误，可能是数据连接已经断开，或者其他未知原因，程序中断了该表的复制！");
                                        break;
                                    }
                                    errorcount++;
                                }
                                for (int i = 0; i < values.Length; i++)
                                {
                                    destCommand.Parameters[i].Value = values[i];
                                }
                                try
                                {
                                    destCommand.ExecuteNonQuery();
                                }
                                catch (Exception ex)
                                {
                                    string value = "";
                                    for (int j = 0; j < values.Length; j++)
                                    {
                                        if (j != 0)
                                        {
                                            value += ";";
                                        }
                                        value += values[j].ToString();

                                    }
                                    BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "复制表" + TableName + "时,发生" + ex.Message + "\r\n" + value);
                                    if (errorcount < 200)
                                    {
                                        continue;
                                    }
                                    else if (errorcount == 200)
                                    {
                                        BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "从" + TableName + "读取数据时,发生了太多的错误，可能是数据连接已经断开，或者其他未知原因，程序中断了该表的复制！");
                                        break;
                                    }
                                    errorcount++;
                                }
                                if (!ISStop)
                                {
                                    break;
                                }

                                count++;
                                BackWorker.ReportProgress(count);
                            }
                        }
                        srcReader.Close();
                        srcReader.Dispose();

                        //如果存在主键或者自增列进行如下处理
                        if (IsKey)
                        {
                            StringBuilder CreateKey = new StringBuilder();
                            for (int i = 0; i < scheamTable.Rows.Count; i++)
                            {
                                DataRow row = scheamTable.Rows[i];
                                string allowDBNull = ((bool)row["AllowDBNull"] == true ? "default NULL" : "NOT NULL");
                                string ColumnName = row["ColumnName"].ToString();
                                string ALTERTABLE = "ALTER TABLE " + destConnection.Database + "." + TableName + " ";
                                if (db_Type == DBTypes.Oracle)
                                {
                                    if ((bool)row["IsKey"])
                                    {
                                        CreateKey.AppendLine(string.Format("{0}ADD CONSTRAINT PK_{1} PRIMARY KEY ({2})", ALTERTABLE, TableName, ColumnName));
                                        break;
                                    }
                                }
                                else
                                {
                                    if ((bool)row["IsAutoIncrement"] || (bool)row["IsKey"])
                                    {
                                        if ((bool)row["IsAutoIncrement"])
                                        {
                                            CreateKey.AppendLine(string.Format("{0} CHANGE `{1}` `{2}` {3} {4} AUTO_INCREMENT", ALTERTABLE, ColumnName, ColumnName, GetDataType(scheamTable, i), allowDBNull));
                                            if ((bool)row["IsKey"])
                                            {
                                                CreateKey.Append(string.Format(" ,ADD PRIMARY KEY ({0})", ColumnName));
                                            }
                                            break;
                                        }
                                        else
                                        {
                                            CreateKey.AppendLine(string.Format("{0}ADD CONSTRAINT PK_{1} PRIMARY KEY ({2})", ALTERTABLE, TableName, ColumnName));
                                            break;
                                        }
                                    }
                                }
                            }
                            destCommand.CommandText = CreateKey.ToString();
                            try
                            {
                                destCommand.ExecuteNonQuery();
                            }
                            catch
                            {
                                throw new Exception("创建表" + TableName + "的主键与自增属性时出错，请手动更改此属性！");
                            }
                        }
                    }
                    ISStop = true;

                }
            }
            catch (Exception ex)
            {
                BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "复制表" + TableName + "时,发生" + ex.Message);
            }
        }

        /// <summary>
        /// 生成创建数据库表结构的 MYSQL 语句。*******************
        /// </summary>
        protected override string GenerateTableSchemaSql(DataTable schemaTable, ref bool IsKey)
        {
            StringBuilder tableSql = new StringBuilder();

            /* 生成创建表定义语句 */
            string tableName = schemaTable.Rows[0]["BaseTableName"].ToString();
            tableSql.Append("CREATE TABLE `").Append(tableName).AppendLine("` (");

            /* 生成各列的定义语句 */
            string columnName, allowDBNull, columnType;
            columnName = String.Empty;
            DataRow row;
            IsKey = false;
            StringBuilder sbPKFields = new StringBuilder();
            try
            {
                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    if (i != 0) tableSql.AppendLine(",");

                    row = schemaTable.Rows[i];
                    columnName = (string)row["ColumnName"];
                    allowDBNull = ((bool)row["AllowDBNull"] == true ? "default NULL" : "NOT NULL");

                    if ((bool)row["IsKey"])
                    {
                        sbPKFields.AppendFormat("{0},", columnName);
                        IsKey = true;
                    }
                    columnType = GetDataType(schemaTable, i);
                    tableSql.AppendFormat("  `{0}` {1} {2}", columnName, columnType, allowDBNull);
                }

                /* 生成主键约束语句 */
                //if (hasKey)
                //{
                //    string pkFields = sbPKFields.ToString().TrimEnd(',');
                //    tableSql.AppendLine(",");
                //    tableSql.Append(" PRIMARY KEY(`").Append(pkFields).AppendLine("`)");
                //}
                tableSql.AppendLine(")ENGINE=InnoDB DEFAULT CHARSET=gb2312;");
            }
            catch (Exception ex)
            {
                BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "表" + tableName + "中" + columnName + "字段" + ex.Message);
                return String.Empty;
            }
            return tableSql.ToString();
        }

        /// <summary>
        /// 抓取目标数据库里字段类型***************
        /// </summary>
        /// <param name="schemaTable"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        protected override string GetDataType(DataTable scheamTable, int i)
        {
            string dataType = GetNativeType(scheamTable.Rows[i]["DataType"].ToString());
            try
            {
                Int32 colsize = (Int32)scheamTable.Rows[i]["ColumnSize"];
                switch (dataType)
                {
                    case "tinyint":

                        dataType = string.Format("{0}({1})", dataType, colsize);
                        break;
                    case "longblob":
                        if (colsize == -1)
                        {
                            break;
                        }
                        if (colsize == 1)
                        {
                            dataType = string.Format("varchar({0})", colsize);
                        }
                        else if (colsize == 255)
                        {
                            dataType = "tinyblob";
                        }
                        else if (colsize == 65535)
                        {
                            dataType = "blob";
                        }
                        else if (colsize == 16777215)
                        {
                            dataType = "blob";
                        }
                        else
                        {
                            dataType = string.Format("varchar({0})", colsize);
                        }

                        break;
                    case "datetime":
                        if (colsize == 10)
                        {
                            dataType = "datetime";
                        }
                        break;
                    case "decimal":
                        dataType = string.Format("{0}({1})", dataType, colsize);
                        break;
                    case "int":
                        if (colsize == 9)
                        {
                            dataType = "mediumint";
                        }
                        else if (colsize == 11)
                        {
                            dataType = "double";
                        }
                        else
                        {
                            dataType = string.Format("int({0})", colsize);
                        }
                        break;
                    case "varchar":
                        if (colsize == 0 || colsize > 8388607)
                        {
                            dataType = "longtext";
                        }
                        else if (colsize == 127)
                        {
                            dataType = "tinytext";
                        }
                        else if (colsize == 32767)
                        {
                            dataType = "text";
                        }
                        else if (colsize == 8388607)
                        {
                            dataType = "mediumtext";
                        }
                        else
                        {
                            dataType = string.Format("{0}({1})", dataType, colsize);
                        }
                        break;
                    case "bit":
                        dataType = string.Format("{0}({1})", dataType, colsize);
                        break;
                    default:
                        //throw new Exception("发现为了未知的数据类型，该表无法复制！");
                        break;
                }
            }
            catch (Exception ex)
            {
                BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "在进行类型转换时发生异常！具体错误信息如下：\r\n" + ex.Message);
            }
            return dataType;
        }


        /// <summary>
        /// 从 .NET 数据类型获取对应的 MySql  类型名称。***********
        /// </summary>
        protected override string GetNativeType(string typeName)
        {
            switch (typeName)
            {
                case "System.Boolean":
                    return "tinyint";
                case "System.Byte[]":
                    return "longblob";
                case "System.DateTime":
                    return "datetime";
                case "System.Decimal":
                    return "decimal";
                case "System.Double":
                    return "double";
                case "System.Int16":
                    return "smallint";
                case "System.Int32":
                    return "int";
                case "System.Int64":
                    return "bigint";
                case "System.Sbyte":
                    return "tinyint";
                case "System.Single":
                    return "float";
                case "System.String":
                    return "varchar";
                case "System.TimeSpan":
                    return "time";
                case "System.UInt64":
                    return "bit";
                default:
                    throw new ApplicationException(string.Format("找不到 {0} 类型对应的 MySql 数据类型。", typeName));
            }
        }
    }
}
